package com.portal.logic;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class SaleDAO {

	//private String ip;
	private Connection connection;
	
	public SaleDAO(Connection connection) {
		//this.ip = ip;
		this.connection = connection;
	}
	
	
	//Create
	public void insert() {
		Sale sale = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\sale.out");
			sale = (Sale) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement preparedStatement = null;

		try {
			Statement st = connection.createStatement(); 
            st.executeUpdate("INSERT INTO tblSales " + 
                "VALUES ("+sale.getId()+", '"+sale.getName()+"','"+sale.getStart()+"','"+sale.getEnd()+"',"+sale.getCountClients()+",'"+sale.getAgeSegmentStart()+"','"+sale.getAgeSegmentEnd()+"','"+sale.getResidentialArea()+"',"+sale.getProductId()+" )"); 

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
	}

	public Sale selectById(int id) {
		Sale sale = new Sale();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = connection.prepareStatement("SELECT * FROM tblSales WHERE id = ?");
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				sale.setId(resultSet.getInt("id"));
				sale.setName(resultSet.getString("name"));
				sale.setStart(resultSet.getString("start"));
				sale.setEnd(resultSet.getString("end"));
				sale.setCountClients(Integer.parseInt(resultSet.getString("count_clients")));
				sale.setAgeSegmentStart(Integer.parseInt(resultSet.getString("age_segment_start")));
				sale.setAgeSegmentEnd(Integer.parseInt(resultSet.getString("age_segment_end")));
				sale.setResidentialArea(resultSet.getString("residentialArea"));
				sale.setProductId(Integer.parseInt(resultSet.getString("product_id")));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}

		return sale;
	}

	public Sale selectByName(String name) {
		Sale sale = new Sale();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = connection.prepareStatement("SELECT * FROM tblSales WHERE name = ?");
			preparedStatement.setString(1, name);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				sale.setId(resultSet.getInt("id"));
				sale.setName(resultSet.getString("name"));
				sale.setStart(resultSet.getString("start"));
				sale.setEnd(resultSet.getString("end"));
				sale.setCountClients(Integer.parseInt(resultSet.getString("count_clients")));
				sale.setAgeSegmentStart(Integer.parseInt(resultSet.getString("age_segment_start")));
				sale.setAgeSegmentEnd(Integer.parseInt(resultSet.getString("age_segment_end")));
				sale.setResidentialArea(resultSet.getString("residentialArea"));
				sale.setProductId(Integer.parseInt(resultSet.getString("product_id")));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}

		return sale;
	}	
	
	//Read All
	public List<Sale> selectAll() {
		Statement statement = null;
		ResultSet resultSet = null;
		List<Sale> sales = new LinkedList<Sale>();
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM tblSales");

			Sale sale = null;
			while(resultSet.next()){
				sale = new Sale();
				sale.setId(Integer.parseInt(resultSet.getString("id")));
				sale.setName(resultSet.getString("name"));
				sale.setStart(resultSet.getString("start"));
				sale.setEnd(resultSet.getString("end"));
				sale.setCountClients(Integer.parseInt(resultSet.getString("count_clients")));
				sale.setAgeSegmentStart(Integer.parseInt(resultSet.getString("age_segment_start")));
				sale.setAgeSegmentEnd(Integer.parseInt(resultSet.getString("age_segment_end")));
				sale.setResidentialArea(resultSet.getString("residentialArea"));
				sale.setProductId(Integer.parseInt(resultSet.getString("product_id")));
				sales.add(sale);
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		System.out.println(sales);
		return sales;
	}


	//Update
	public void update() {
		Sale sale = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\editSale.out");
			sale = (Sale) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("UPDATE tblSales SET " +
					"name = ?, start = ?, end = ?, count_clients = ?, length_conversion = ?, age_segment_start = ?, age_segment_end = ?, residentialArea = ?, product_id = ? WHERE id = ?");

			preparedStatement.setString(1, sale.getName());
			preparedStatement.setString(2, sale.getStart());
			preparedStatement.setString(3, sale.getEnd());
			preparedStatement.setInt(4, sale.getCountClients());
			preparedStatement.setInt(5, sale.getAgeSegmentStart());
			preparedStatement.setInt(6, sale.getAgeSegmentEnd());
			preparedStatement.setString(7, sale.getResidentialArea());
			preparedStatement.setInt(8, sale.getProductId());
			preparedStatement.setInt(9, sale.getId());
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}

	//Delete
	public void delete(int id) {
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("DELETE FROM tblSales WHERE id = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}

	
	public int selectMaxId() {
		Statement statement = null;
		ResultSet resultSet = null;
		int id = 0;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT max(id) as max_val FROM tblSales");

			while(resultSet.next()){
				id = Integer.parseInt(resultSet.getString("max_val"));
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		
		return id;
	}
}
